<?php

/*
 * core/database_fct.php
 *
 * database accessor functions
 *
 */

class CoreDBFct
{
	/*
	 * Service related
	 */
	public static function isServiceExist( $serviceName )
	{
		$serviceName = db()->escapeStr( $serviceName );
		$sql = 'SELECT id FROM `core_Service` WHERE codeName="'.$serviceName.'"';
		$result = db()->select( $sql );
		if( !$result ) 
		{
			Log::error( 'db error: '.db()->lastError() );
			return null;
		}

		if( $result->rowCount() == 0 ) return -1;
		return $result->getVal( 'id' );
	}

	public static function insertService( $codeName, $userName, $help )
	{
		$sql = 'SELECT MAX(id) FROM `core_Service`';
		$result = db()->select( $sql );

		$nextid = $result->getVal( 'MAX(id)' ) + 1;
		$codeName = db()->escapeStr( $codeName );
		$userName = db()->escapeStr( $userName );
		$help = db()->escapeStr( $help );

		$sql = 'INSERT INTO `core_Service` (id,codeName,userName,help) VALUES ('.$nextid.',"'.$codeName.'","'.$userName.'","'.$help.'")';
		if( !db()->execute( $sql ) ) 
		{
			Log::error( 'db error: '.db()->lastError() );
			return null;
		}

		return $nextid;
	}

	public static function updateService( $codeName, $userName, $help )
	{
		$codeName = db()->escapeStr( $codeName );
		$userName = db()->escapeStr( $userName );
		$help = db()->escapeStr( $help );

		$sql = 'UPDATE `core_Service` SET userName="'.$userName.'", help="'.$help.'" WHERE codeName="'.$codeName.'"';
		if( !db()->execute( $sql ) ) 
		{
			Log::error( 'db error: '.db()->lastError() );
			return false;
		}
		return true;
	}

	/*
	 * Service's Methods related
	 */
	public static function isServiceMethodExist( $serviceId, $methodName )
	{
		$methodName = db()->escapeStr( $methodName );
		$sql = 'SELECT id FROM `core_ServiceMethod` WHERE idService='.$serviceId.' AND codeName="'.$methodName.'"';
		$result = db()->select( $sql );
		if( !$result ) 
		{
			Log::error( 'db error: '.db()->lastError() );
			return false;
		}

		if( $result->rowCount() == 0 ) return -1;
		return $result->getVal( 'id' );
	}

	public static function insertServiceMethod( $serviceId, $codeName, $userName, $help, $public )
	{
		$sql = 'SELECT MAX(id) FROM `core_ServiceMethod`';
		$result = db()->select( $sql );

		$nextid = $result->getVal( 'MAX(id)' ) + 1;
		$codeName = db()->escapeStr( $codeName );
		$userName = db()->escapeStr( $userName );
		$help = db()->escapeStr( $help );
		$public = ($public) ? 'TRUE' : 'FALSE';

		$sql = 'INSERT INTO `core_ServiceMethod` (id,idService,codeName,userName,help,`public`) VALUES ('.$nextid.','.$serviceId.',"'.$codeName.'","'.$userName.'","'.$help.'",'.$public.')';
		if( !db()->execute( $sql ) ) 
		{
			Log::error( 'insertServiceMethod: db error: '.db()->lastError() );
			return null;
		}

		return $nextid;
	}

	public static function updateServiceMethod( $serviceId, $codeName, $userName, $help, $public )
	{
		$codeName = db()->escapeStr( $codeName );
		$userName = db()->escapeStr( $userName );
		$help = db()->escapeStr( $help );
		$public = ($public) ? 'TRUE' : 'FALSE';

		$sql = 'UPDATE `core_ServiceMethod` SET userName="'.$userName.'", help="'.$help.'", `public`='.$public.' WHERE idService='.$serviceId.' AND codeName="'.$codeName.'"';
		if( !db()->execute( $sql ) ) 
		{
			Log::error( 'updateServiceMethod: db error: '.db()->lastError() );
			return false;
		}
		return true;
	}

	/*
	 * User related
	 */

	// param: user: id or username
	public static function getUser( $user )
	{
		if( is_int($user) )
		{
			$sql = 'SELECT id,name,email FROM core_User WHERE id='.$user;
		}
		else
		{
			$sql = 'SELECT id,name,email FROM core_User WHERE name="'.$user.'"';
		}

		$result = db()->select( $sql );
		if( !$result )
		{
			Log::error( 'db error: '.db()->lastError() );
			return null;
		}

		$ret = array( 'id'=>				$result->getVal( 'id' ),
									'username'=>	$result->getVal( 'name' ),
									'email'=>			$result->getVal( 'email' ) );

		return $ret;
	}

	public static function getUserId( $username )
	{
		$username = db()->escapeStr( $username );
		$sql = 'SELECT id FROM `core_User` WHERE name="'.$username.'"';
		$result = db()->select( $sql );
		if( !$result ) 
		{
			Log::error( 'db error: '.db()->lastError() );
			return -1;
		}

		if( $result->rowCount() == 0 ) return -1;
		return $result->getVal( 'id' );
	}

	public static function checkPassword( $username, $password )
	{
		$username = db()->escapeStr( $username );
		$password = db()->escapeStr( $password );

		$sql = 'SELECT 1 FROM `core_User` WHERE name="'.$username.'" AND password=md5("'.$password.'")';

		$result = db()->select( $sql );
		if( !$result )
		{
			Log::error( 'db error: '.db()->lastError() );
			return false;
		}

		if( $result->rowCount() < 1 )
			return false;

		return true;
	}

	public static function insertUser( $username, $password, $email )
	{
		$sql = 'SELECT MAX(id) AS maxID FROM `core_User`';
		$result = db()->select( $sql );

		$nextid = $result->getVal( 'maxID' ) + 1;
		$username = db()->escapeStr( $username );
		$password = db()->escapeStr( $password );
		$email = db()->escapeStr( $email );

		$sql = 'INSERT INTO `core_User` (id,name,password,email) VALUES ('.$nextid.',"'.$username.'",MD5("'.$password.'"),"'.$email.'")';

		if( !db()->execute($sql) )
		{
			Log::error( 'db error: '.db()->lastError() );
			return -1;
		}

		return $nextid;
	}

	public static function updateUser( $username, $password, $email=null )
	{
		$username = db()->escapeStr( $username );
		$password = db()->escapeStr( $password );

		if( !is_null($email) )
		{
			$email = db()->escapeStr( $email );
			$sql = 'UPDATE `core_User` SET password=md5("'.$password.'"), email="'.$email.'" WHERE name="'.$username.'"';
		}
		else
		{
			$sql = 'UPDATE `core_User` SET password=md5("'.$password.'") WHERE name="'.$username.'"';
		}

		if( !db()->execute($sql) )
		{
			Log::error( 'db error: '.db()->lastError() );
			return false;
		}
		return true;
	}

	public static function removeUser( $username )
	{
		$username = db()->escapeStr( $username );

		$sql = 'DELETE FROM `core_User` WHERE name="'.$username.'"';
		if( !db()->execute($sql) )
		{
			Log::error( 'db error: '.db()->lastError() );
			return false;
		}
		return true;
	}

	public static function listUsers()
	{
		$sql = 'SELECT id,name,email FROM `core_User`';
		$result = db()->select( $sql );

		if( !$result )
		{
			Log::error( 'db error: '.db()->lastError() );
			return null;
		}

		$ret = array();
		while( ($row = $result->getNext()) )
		{
			array_push( $ret, array('id'=>$row['id'], 'username'=>$row['name'], 'email'=>$row['email']) );
		}

		return $ret;
	}

	/*
	 * Group related
	 */
	public static function getGroupId( $groupname )
	{
		$groupname = db()->escapeStr( $groupname );
		$sql = 'SELECT id FROM `core_Group` WHERE name="'.$groupname.'"';
		$result = db()->select( $sql );
		if( !$result ) 
		{
			Log::error( 'db error: '.db()->lastError() );
			return -1;
		}

		if( $result->rowCount() == 0 ) return -1;
		return $result->getVal( 'id' );
	}

	// getGroup: describe a group
	// param: group: can be groupId or groupName
	public static function getGroup( $group )
	{
	}

	public static function insertGroup( $groupname )
	{
		$sql = 'SELECT MAX(id) FROM `core_Group`';
		$result = db()->select( $sql );

		$nextid = $result->getVal( 'MAX(id)' ) + 1;
		$groupname = db()->escapeStr( $groupname );

		$sql = 'INSERT INTO `core_Group` (id,name) VALUES ('.$nextid.',"'.$groupname.'")';

		if( !db()->execute($sql) )
		{
			Log::error( 'db error: '.db()->lastError() );
			return -1;
		}

		return $nextid;
	}

	public static function isUserInGroup( $userID, $groupID )
	{
		$sql = 'SELECT 1 FROM `core_RelGroupUser` WHERE idGroup='.$groupID.' AND idUser='.$userID;

		$r = db()->select( $sql );
		if( is_null($r) )
		{
			Log::error( 'db error: '.db()->lastError() );
			return false;
		}

		if( $r->rowCount() == 0 ) return false;
		return true;
	}

	public static function addUserToGroup( $userID, $groupID )
	{
		// TODO: check if userID and groupID are valid?
		$sql = 'INSERT INTO `core_RelGroupUser` (idGroup,idUser) VALUES ('.$groupID.','.$userID.')';

		if( !db()->execute($sql) )
		{
			Log::error( 'db error: '.db()->lastError() );
			return false;
		}

		return true;
	}

	/*
	 * Permissions related
	 */
	public static function isCallAllowed( $groupId, $serviceId, $methodId )
	{
		$sql = 'SELECT 1 FROM `core_CallPermission` WHERE idGroup='.$groupId.' AND idService='.$serviceId.' AND idMethod='.$methodId;
		$result = db()->select( $sql );
		if( !$result ) 
		{
			Log::error( 'db error: '.db()->lastError() );
			return false;
		}

		if( $result->rowCount() == 0 ) return false;
		return true;
	}

	public static function isCallAllowedByName( $group, $service, $method )
	{
		$sql = 'SELECT 1 FROM `core_CallPermission` AS p, `core_Group` AS g, `core_Service` AS svc, `core_ServiceMethod` AS mth WHERE ';
		$sql .= 'p.idGroup=g.id AND p.idService=svc.id AND p.idMethod=mth.id AND ';
		$sql .= 'g.name="'.$group.'" AND svc.codeName="'.$service.'" AND mth.codeName="'.$method.'"';

		$result = db()->select( $sql );
		if( !$result ) 
		{
			Log::error( 'db error: '.db()->lastError() );
			return false;
		}

		if( $result->rowCount() == 0 ) return false;
		return true;
	}

	public static function insertCallPermission( $groupId, $serviceId, $methodId )
	{
		$sql = 'INSERT INTO `core_CallPermission` (idGroup,idService,idMethod) VALUES ('.$groupId.','.$serviceId.','.$methodId.')';

		if( !db()->execute($sql) ) 
		{
			Log::error( 'db error: '.db()->lastError() );
			return false;
		}
		return true;
	}

	public static function insertCallPermissionByName( $group, $service, $method )
	{
		$sql = 'INSERT INTO `core_CallPermission` (idGroup,idService,idMethod) VALUES (';
		$sql .= '(SELECT id FROM `core_Group` WHERE name="'.$group.'"),';
		$sql .= '(SELECT id FROM `core_Service` WHERE codeName="'.$service.'"),';
		$sql .= '(SELECT mth.id FROM `core_ServiceMethod` AS mth, `core_Service` AS svc WHERE mth.codeName="'.$method.'" AND mth.idService=svc.id AND svc.codeName="'.$service.'") )';

		if( !db()->execute($sql) )
		{
			Log::error( 'db error: '.db()->lastError() );
			return false;
		}
		return true;
	}

	public static function listCallPermissionsForGroup( $group )
	{
		if( is_int($group) )
		{
			$sql = 'SELECT cp.idService, svc.codeName AS serviceCodeName, cp.idMethod, mth.codeName AS methodCodeName FROM core_CallPermission AS cp, core_Service AS svc, core_ServiceMethod AS mth WHERE cp.idGroup='.$group.' AND cp.idService=svc.id AND cp.idMethod=mth.id';
		}
		else
		{
			$sql = 'SELECT cp.idService, svc.codeName AS serviceCodeName, cp.idMethod, mth.codeName AS methodCodeName FROM core_CallPermission AS cp, core_Service AS svc, core_ServiceMethod AS mth, core_Group AS grp WHERE grp.name="'.$group.'" AND cp.idGroup=grp.id AND cp.idService=svc.id AND cp.idMethod=mth.id';
		}
		$result = db()->select( $sql );

		if( !$result )
		{
			Log::error( 'unable to retreive permissions for group "'.$group.'"' );
			return null;
		}

		$ret = array();
		while( ($row=$result->getNext()) )
		{
			$r = array( 'service' => array( 'id'=>$row['idService'], 'name'=>$row['serviceCodeName'] ),
									'method' => array( 'id'=>$row['idMethod'], 'name'=>$row['methodCodeName'] ) );
			array_push( $ret, $r );
		}

		return $ret;
	}

	public static function listCallPermissionsForUser( $user )
	{
		if( is_int($user) )
		{
			$sql = 'SELECT cp.idService, svc.codeName AS serviceCodeName, cp.idMethod, mth.codeName AS methodCodeName FROM core_CallPermission AS cp, core_Service AS svc, core_ServiceMethod AS mth, core_User as usr, core_RelGroupUser AS grpusr WHERE usr.id='.$user.' AND usr.id=grpusr.idUser AND grpusr.idGroup=cp.idGroup AND cp.idService=svc.id AND cp.idMethod=mth.id';
		}
		else
		{
			$sql = 'SELECT cp.idService, svc.codeName AS serviceCodeName, cp.idMethod, mth.codeName AS methodCodeName FROM core_CallPermission AS cp, core_Service AS svc, core_ServiceMethod AS mth, core_User as usr, core_RelGroupUser AS grpusr WHERE usr.name="'.$user.'" AND usr.id=grpusr.idUser AND grpusr.idGroup=cp.idGroup AND cp.idService=svc.id AND cp.idMethod=mth.id';
		}
		$result = db()->select( $sql );

		if( !$result )
		{
			Log::error( 'unable to retreive permissions for user "'.$user.'"' );
			return null;
		}

		$ret = array();
		while( ($row=$result->getNext()) )
		{
			$r = array( 'service' => array( 'id'=>$row['idService'], 'name'=>$row['serviceCodeName'] ),
									'method' => array( 'id'=>$row['idMethod'], 'name'=>$row['methodCodeName'] ) );
			array_push( $ret, $r );
		}

		return $ret;
	}

	/*
	 * Visitor related
	 */
	public static function insertVisitor( $clientAddress, $clientPort, $clientHostname, $httpReferer, $httpUserAgent, $requestMethod, $requestURI )
	{
		$sql = 'INSERT INTO `core_Visitor` (clientAddress, clientPort, clientHostname, httpReferer, httpUserAgent, requestMethod, requestURI ) VALUES ';
		$sql .= '("'.$clientAddress.'",'.$clientPort.',"'.$clientHostname.'","'.$httpReferer.'","'.$httpUserAgent.'","'.$requestMethod.'","'.$requestURI.'")';

		if( !db()->execute( $sql ) )
		{
			Log::error( 'unable to insert visitor' );
			return false;
		}

		return true;
	}

	public static function listVisitor()
	{
		$sql = 'SELECT clientAddress,clientPort,clientHostname,httpReferer,httpUserAgent,requestMethod,requestURI FROM `core_Visitor`';
		$result = db()->select( $sql );
		if( !$result )
		{
			Log::error( 'unable to retreive visitors' );
			return null;
		}

		$r = array();
		while( ($row = $result->getNext()) )
		{
			array_push( $r, $row );
		}

		return $r;
	}

	public static function getNumberVisitor()
	{
		$sql = 'SELECT COUNT(*) AS c FROM `core_Visitor`';
		$result = db()->select( $sql );
		if( !$result )
		{
			return 0;
		}

		return (int) $result->getVal('c');
	}
};

?>
